# coding: utf-8
from django.db.models import AutoField
from django.db.models import Q

from jd_oss.tools import *
from .models import AssetInfo

import datetime
import xlrd
import xlsxwriter


def sort_ip_list(ip_list):
    """ ip地址排序 """
    ip_list.sort(key=lambda s: map(int, s.split('.')))
    return ip_list


def excel_to_db(excel_file):
    """
    Asset add batch function
    """
    try:
        data = xlrd.open_workbook(
            filename=None, file_contents=excel_file.read())
    except Exception, e:
        return False
    else:
        table = data.sheets()[0]
        rows = table.nrows
        for row_num in range(1, rows):
            row = table.row_values(row_num)
            if row:
                it_type, it_serial, it_size, it_detail,
                it_cpu, it_mem, it_dep,
                it_user, it_reason, it_dec, it_usetime,
                it_cost, it_channel = row
                if AssetInfo.objects.filter(it_serial=it_serial):
                    continue
                if it_serial:
                    asset = AssetInfo(it_type=it_type,
                                      it_serial=it_serial,
                                      it_size=it_size,
                                      it_detail=it_detail,
                                      it_cpu=it_cpu,
                                      it_mem=it_mem,
                                      it_dep=it_dep,
                                      it_user=it_user,
                                      it_reason=it_reason,
                                      it_dec=it_dec,
                                      it_usetime=it_usetime,
                                      it_cost=it_cost,
                                      it_channel=it_channel
                                      )
                    asset.save()
        return True


def write_excel(asset_all):
    data = []
    now = datetime.datetime.now().strftime('%Y_%m_%d_%H_%M')
    file_name = 'cmdb_excel_' + now + '.xlsx'
    workbook = xlsxwriter.Workbook('static/files/excels/%s' % file_name)
    worksheet = workbook.add_worksheet(u'CMDB数据')
    worksheet.set_first_sheet()
    worksheet.set_column('A:E', 15)
    worksheet.set_column('F:F', 40)
    worksheet.set_column('G:Z', 15)
    # model field dict
    field_dict = get_modelfield('jd_asset', 'AssetInfo')
    title = field_dict.values()
    for asset in asset_all:
        alter_dic = [asset.__dict__[field] for field in field_dict.keys()]
        data.append(alter_dic)
    format = workbook.add_format()
    format.set_border(1)
    format.set_align('center')
    format.set_align('vcenter')
    format.set_text_wrap()

    format_title = workbook.add_format()
    format_title.set_border(1)
    format_title.set_bg_color('#cccccc')
    format_title.set_align('center')
    format_title.set_bold()

    format_ave = workbook.add_format()
    format_ave.set_border(1)
    format_ave.set_num_format('0.00')

    worksheet.write_row('A1', title, format_title)
    i = 2
    for alter_dic in data:
        location = 'A' + str(i)
        worksheet.write_row(location, alter_dic, format)
        i += 1

    workbook.close()
    ret = (True, file_name)
    return ret


def search_asset(request):
    """
    资产搜索公用函数.
    获取 keyfield ，keyword
    返回找到的所有模型实例.
    """
    keyfield = request.GET.get('keyfield', '')
    keyword = request.GET.get('keyword', '').strip()
    if keyword:
        if keyfield:
            asset_find = AssetInfo.objects.filter(
                **{'%s__contains' % keyfield: keyword})
        else:
            # 模糊搜索
            asset_find = AssetInfo.objects.filter(
                Q(it_id__contains=keyword) |
                Q(it_serial__contains=keyword) |
                Q(it_type__contains=keyword) |
                Q(it_size__contains=keyword) |
                Q(it_brand__contains=keyword) |
                Q(it_os__contains=keyword) |
                Q(it_cpu__contains=keyword) |
                Q(it_mem__contains=keyword) |
                Q(it_ip__contains=keyword) |
                Q(it_mac__contains=keyword) |
                Q(it_dep__contains=keyword) |
                Q(it_user__contains=keyword) |
                Q(it_status__contains=keyword) |
                Q(it_reason__contains=keyword) |
                Q(it_usetime__contains=keyword) |
                Q(it_buytime__contains=keyword) |
                Q(it_cost__contains=keyword) |
                Q(it_channel__contains=keyword) |
                Q(it_detail__contains=keyword) |
                Q(it_dec__contains=keyword)
            )
    else:
        asset_find = AssetInfo.objects.all()

    return asset_find
